package com.wzapp.sitposture.setup.repository;

import com.wzapp.sitposture.setup.entity.City;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import java.util.List;

/**
 * Created by qianglong on 2018/1/19.
 */
@RepositoryRestResource(exported = false)
public interface CityRepository extends JpaRepository<City,String> {

    /**
     * 功能: 根据版本查询城市列表<br/>
     *
     * @author ql
     * @create 2018-05-08 20:20
     * @param version
     * @return java.util.List<City>
     **/
    List<City> findCitiesByVersionLessThanEqual(String version);

    /**
     * 功能:根据省份，城市查询城市 <br/>
     *
     * @author ql
     * @create 2018-05-08 20:20
     * @param province
     * @param city
     * @return City
     **/
    City findTop1ByProvinceStartingWithAndCityStartingWith(String province, String city);

    /**
     * 功能: 根据城市名查询<br/>
     *
     * @author ql
     * @create 2018/8/23 9:34
     * @param city
     * @return City
     **/
    City findTop1ByCityStartingWith(String city);

    /**
     * 功能: 查询所有的省份列表<br/>
     *
     * @author ql
     * @create 2018-05-08 20:20
     * @param
     * @return java.util.List<City>
     **/
    //mysql写法
    //@Query(value = "SELECT * from config_city GROUP BY province_code ORDER BY province_code",nativeQuery = true)
    /**
     * postgres写法
     */
    @Query(nativeQuery = true,value = "SELECT t.id,t.city,t.city_index,t.pinyin,t.province,t.province_code,t.sort_num,t.version,t.create_time,t.update_time from (SELECT *, Row_Number() OVER (partition by province_code ORDER BY config_city) rank FROM config_city) t where t.rank =1")
    List<City> findProvince();

    /**
     * 功能: 根据省份code查询城市列表<br/>
     *
     * @author ql
     * @create 2018-05-08 20:21
     * @param proviceCode
     * @return java.util.List<City>
     **/
    List<City> findCitiesByProvinceCodeOrderBySortNum(String proviceCode);
}
